﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using NHibernate;
using NHibernate.Engine;
using System.Data.OracleClient;

namespace BEGDC.Utility.NHibernate
{
	/// <summary>
	/// Summary description for NHibernateUtil.
	/// </summary>
	public class DAO
	{
        public static DataTable ExecuteDataTable(ISession dbSession, string queryString, string tablename)
        {
            System.Data.DataTable dt = ExecuteDataTable(dbSession, queryString);

            if (dt != null)
                dt.TableName = tablename;

            return dt;
        }

		public static DataTable ExecuteDataTable(ISession dbSession, string queryString)
		{
			DataTable table = null;
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
			cmd.CommandText = queryString;
			IDbConnection conn = dbSession.Connection;
					
			cmd.Connection = conn;
			if (dbSession.Transaction != null)
				dbSession.Transaction.Enlist(cmd);
					
			if(cmd.GetType() == typeof(SqlCommand))	
			{
				SqlDataAdapter da = null;
#if (SQLServer)
                da = new SqlDataAdapter((SqlCommand)cmd);
#else
                da = new System.Data.OracleClient.OracleDataAdapter((SqlCommand)cmd);
#endif
				DataSet ds = new DataSet();
				da.Fill(ds);
				if(ds.Tables[0] != null)
					//table = ds.Tables[0].Copy(); 
					table = ds.Tables[0]; 
			}			
			return table;
		}

        public static DataSet ExecuteDataSet(ISession dbSession, string queryString)
        {
            DataSet ds = new DataSet();
            
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.CommandText = queryString;
            IDbConnection conn = dbSession.Connection;

            cmd.Connection = conn;
            if (dbSession.Transaction != null)
                dbSession.Transaction.Enlist(cmd);

            if (cmd.GetType() == typeof(SqlCommand))
            {
                SqlDataAdapter da = null;
#if (SQLServer)
                da = new SqlDataAdapter((SqlCommand)cmd);
#else
                da = new System.Data.OracleClient.OracleDataAdapter((SqlCommand)cmd);
#endif               
                da.Fill(ds);

                ds.Tables[0].TableName = "ReturnDataTable";
            }
            return ds;
        }

		public static IList ExecuteQuery(ISession dbSession, string queryString)
		{
			IList result = new ArrayList();
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.CommandText = queryString;
			IDbConnection conn = dbSession.Connection;
			cmd.Connection = conn;
			if (dbSession.Transaction != null)
				dbSession.Transaction.Enlist(cmd);

			IDataReader rs = cmd.ExecuteReader();
			while ( rs.Read() ) 
			{
				int fieldCount = rs.FieldCount;
				object[] values = new Object[ fieldCount ];
				for ( int i = 0; i < fieldCount; i ++ )
					values[i] = rs.GetValue(i);
				result.Add( values );
			}
			return result;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbSession"></param>
        /// <param name="storeProcedureName"></param>
        /// <param name="parametersPairs">(parameterName, value)</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(ISession dbSession, string storeProcedureName, params Tuple<string, object>[] parametersPairs)
        {
            IDbCommand cmd = null;
            IDataParameter[] parameters = null;
#if (SQLServer)
            cmd = new SqlCommand();
            
            if (parametersPairs != null && parametersPairs.Length > 0)
            {
                parameters = new SqlParameter[parametersPairs.Length];
                for (int i = 0; i < parametersPairs.Length;i++ )
                {
                    Tuple<string, object> parameterPair = parametersPairs[i];
                    parameters[i] = new SqlParameter(parameterPair.Item1, parameterPair.Item2);
                }
            }
#else
            cmd = new System.Data.OracleClient.OracleCommand();

            parameters = new OracleParameterCollection();
            if (parametersPairs != null && parametersPairs.Length > 0)
            {
                foreach (Tuple<string, object> parameterPair in parametersPairs)
                {
                    OracleParameter parameter = new OracleParameter(parameterPair.Item1, parameterPair.Item2);
                    ((SqlParameterCollection)parameters).Add(parameter);
                }
            }
#endif
            cmd.Connection = dbSession.Connection;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storeProcedureName;
            if (parameters != null && parameters.Length > 0)
            {
                foreach (IDbDataParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            if (dbSession.Transaction != null)
            {
                dbSession.Transaction.Enlist(cmd);
            }
            return cmd.ExecuteNonQuery();
        }

        public static object ExecuteScalar(ISession dbSession, string queryString)
        {
            IDbCommand cmd = null;
#if(SQLServer)
            cmd = new SqlCommand();
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.Connection = dbSession.Connection;
            cmd.CommandText = queryString;
            IDbConnection conn = dbSession.Connection;
            if (dbSession.Transaction != null)
            {
                dbSession.Transaction.Enlist(cmd);
            }
            return cmd.ExecuteScalar();
        }

		public static int ExecuteNonQuery(ISession dbSession, string sqltring)
		{
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.CommandText = sqltring;
			IDbConnection conn = dbSession.Connection;
			cmd.Connection = conn;
			if (dbSession.Transaction != null)
				dbSession.Transaction.Enlist(cmd);
			return cmd.ExecuteNonQuery();
		}

		public static void FillDataSet(ISession dbSession, string queryString, DataSet dataSet)
		{
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.CommandText = queryString;
			IDbConnection conn = dbSession.Connection;
					
			cmd.Connection = conn;
			if (dbSession.Transaction != null)
				dbSession.Transaction.Enlist(cmd);
					
			if(cmd.GetType() == typeof(SqlCommand))	
			{
                SqlDataAdapter da = null;
#if (SQLServer)
                da = new SqlDataAdapter((SqlCommand)cmd);
#else
                da = new System.Data.OracleClient.OracleDataAdapter((SqlCommand)cmd);
#endif       
                da.Fill(dataSet);
			}
		}

        public static void FillDataSet(ISession dbSession, string queryString, DataSet dataSet, string tableName)
        {
            IDbCommand cmd = null;
#if (SQLServer)
            cmd = new SqlCommand();// dbSession.SessionFactory.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, (global::NHibernate.SqlCommand.SqlString)queryString, (global::NHibernate.SqlTypes.SqlType[])null);
#else
            cmd = new System.Data.OracleClient.OracleCommand();
#endif
            cmd.CommandText = queryString;
            IDbConnection conn = dbSession.Connection;

            cmd.Connection = conn;
            if (dbSession.Transaction != null)
                dbSession.Transaction.Enlist(cmd);

            if (cmd.GetType() == typeof(SqlCommand))
            {
                SqlDataAdapter da = null;
#if (SQLServer)
                da = new SqlDataAdapter((SqlCommand)cmd);
#else
                da = new System.Data.OracleClient.OracleDataAdapter((SqlCommand)cmd);
#endif       
                da.Fill(dataSet, tableName);
            }
        }
	}
}
